package com.blogspot.nhu313.myhistory.service.db;

import java.util.List;


public class SQLStringBuilder {
	private static final char CLOSE_PARANTHESE = ')';
	private static final char OPEN_PARANTHESE = '(';
	public static final String SEPARATOR = ", ";
	private static final String SEMI_COLIN = ";";
	private final static String SELECT = "SELECT ";
	private final static String FROM = " FROM ";
	private final static String WHERE = " WHERE ";

	private final static String CREATE_TABLE = "CREATE TABLE IF NOT EXISTS ";
	private final static String DROP_TABLE = "DROP TABLE IF EXISTS ";
	
	public static String query(String tableName, String columns){
		StringBuilder queryString = getBasicSelectString(tableName, columns);
		queryString.append(SEMI_COLIN);
		return queryString.toString();
	}
	
	public static String queryAllColumns(String tableName){
		return query(tableName, "*");
	}
	
	/**
	 * Ex: "SELECT * FROM Person WHERE id = 3;"
	 * tableName: Person
	 * columns: *
	 * whereCondition: id = 3
	 * @param tableName
	 * @param columns columns to retrieve data from
	 * @param whereCondition String represent the where condition without the word WHERE. 
	 * @return an SQL Select string to retrieve data from the database
	 */
	public static String queryWithCondition(String tableName, String columns, String whereCondition){
		StringBuilder queryString = getBasicSelectString(tableName, columns);
		queryString.append(WHERE);
		queryString.append(whereCondition);
		queryString.append(SEMI_COLIN);
		return queryString.toString();
	}
	
	private static StringBuilder getBasicSelectString(String tableName, String columns){
		StringBuilder query = new StringBuilder();
		
		query.append(SELECT);
		query.append(columns);
		query.append(FROM);
		query.append(tableName);
		
		return query;
	}
		
	/**
	 * Delete table from the database
	 * @param tableName non null value
	 * @return an SQL string to delete the table from the database. 
	 * If string is null, the SQL statement will return a string containing null as the table name. 
	 */
	public static String deleteTable(String tableName){
		return DROP_TABLE + tableName;
	}
	
	/**
	 * @param tableName not null
	 * @param columns not null or empty
	 * @return SQL string to create table. The column is order is based on the order of the list.
	 * @throws NullPointerException if tableName or columns is null
	 */
	public static String createTable(String tableName, List<DBField> columns){
		StringBuilder sqlString = new StringBuilder();
		sqlString.append(CREATE_TABLE).append(tableName);
		sqlString.append(OPEN_PARANTHESE);
		appendColumns(columns, sqlString);
		sqlString.append(CLOSE_PARANTHESE);
		sqlString.append(SEMI_COLIN);
		return sqlString.toString();
	}

	private static void appendColumns(List<DBField> columns, StringBuilder sqlString) {
		final int columnsSize = columns.size();
		for (int i = 0; i < columnsSize; i++){
			if (i != 0){
				sqlString.append(SEPARATOR);
			}

			DBField col = columns.get(i);
			appendColumnValues(sqlString, col);
		}
	}

	private static void appendColumnValues(StringBuilder sqlString, DBField col) {
		sqlString.append(col.getName());
		sqlString.append(' ');
		sqlString.append(col.getType());
	}
}
